<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Moderators_mod extends CI_Model {

    private $_dbTableUsers = 'users';
    private $_dbTableImages = 'images';
    private $_dbTableTeams = 'teams';
    private $_dbTableMessages = 'messages';
    private $_dbTableActivity = 'mod_activity';
    private $_dbTableIns = 'mod_ins';
    private $_dbTableLogOnline = 'log_online';
    private $_dbTableProfileViews='profile_views';
    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getTeams() {
        $query = $this->db->select('*')
                ->from($this->_dbTableTeams)
                //->where()
                ->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

    public function getOneTeam($id, $like_array = false) {
        $query = $this->db->select('*')
                ->from($this->_dbTableTeams)
                ->where('id', $id)
                ->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() == 1) { // ONE ROW
            if (true == $like_array) // 
                $result = $query->row_array(); // RETURN ONE ROW ARRAY
            else
                $result = $query->row(); // RETURN ONE ROW object
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

    


    public function getConversations() {
        $id = $this->session->userdata('user_id');
        $team_id = $this->session->userdata('team_id');
        $sql = "
              SELECT * FROM
              (SELECT DISTINCT 
                        users1.id as fake, users2.id as user,  
                       ( SELECT username  FROM {$this->_dbTableUsers} WHERE `id`=  users1.id ) as username_fake,
                       ( SELECT username  FROM {$this->_dbTableUsers} WHERE `id`=  users2.id ) as username_user,   
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`=  users1.id ORDER BY main desc LIMIT 0,1 ) as image_name_fake,
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`=  users2.id ORDER BY main desc LIMIT 0,1 ) as image_name_user   
                FROM  {$this->_dbTableMessages}, {$this->_dbTableUsers} users1, {$this->_dbTableUsers} users2
                WHERE 
                ( users1.id IN (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.is_fake=1 
                    AND
                    {$this->_dbTableUsers}.team_id= {$team_id}) 
                  AND ((users1.id={$this->_dbTableMessages}.`from` AND users2.id={$this->_dbTableMessages}.`to`) OR (users1.id={$this->_dbTableMessages}.`to` AND users2.id={$this->_dbTableMessages}.`from`))
                    
                    /* AND
                        {$this->_dbTableMessages}.seen=0 */
                      
                      AND
                      (                       
                        {$this->_dbTableMessages}.`del_to` = 0
                      OR  
                        {$this->_dbTableMessages}.`del_from` = 0 
                      )  
                      AND NOT EXISTS (SELECT id FROM {$this->_dbTableActivity} WHERE 
                          users1.id=$this->_dbTableActivity.`fake_user_id` 
                          AND users2.id=$this->_dbTableActivity.`user_id`
                          AND (
                                  {$this->_dbTableActivity}.`active`=1
                                      AND
                                  {$this->_dbTableActivity}.`mod_id`!=$id
                                      )
                          AND {$this->_dbTableActivity}.`taken`=1)    
                
               )) AS sub                       
                
               
                ;
               ";
        $query = $this->db->query($sql);
        $result = $query->result();
        /*
          if ($query->num_rows() > 0) {
          $users = $query->result();
          $query->free_result();
          } else {
          $users = NULL;
          }
          $result = array();
          $row = new stdClass();
          foreach ($users as $user) {
          $row = $user;
          $query = $this->db->select(" COUNT(*) as num_messages ")
          ->from($this->_dbTableSupport)
          ->where('from = ' . $user->id_from)
          ->where($this->_dbTableSupport . ".to = " . $adminId)

          ->get();
          //echo $this->db->last_query();
          $errors = $this->db->_error_message();
          if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
          } else {

          }

          if ($query->num_rows() > 0) {
          $count = $query->row();
          $row->num_messages = $count->num_messages;
          $query->free_result();
          } else {

          $row->num_messages = 0;
          }
          $result[] = $row;
          } */
        return $result;
    }

    public function getConversation($fromId, $toId) {

        $where = "( ({$this->_dbTableMessages}.to = {$toId} AND {$this->_dbTableMessages}.from = {$fromId}) AND `del_to` = 0 )
            OR  
            ( ({$this->_dbTableMessages}.to = {$fromId} AND {$this->_dbTableMessages}.from = {$toId}) AND `del_from` = 0 )";


        // Postavi da su sve poruke procitane koje su poslate od tog usera, jer ih sad gledam (prikazujem)
        $sql_update = "UPDATE {$this->_dbTableMessages} 
                         SET seen = 1 
                         WHERE (
                                   {$this->_dbTableMessages}.to = {$toId} /* one koje su poslate laznom useru */
                                AND 
                                   {$this->_dbTableMessages}.from = {$fromId} /* od tog korisnika */
                                )
                               
                         ;  
                        ";
        $query = $this->db->query($sql_update);
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }


        $this->db->select
                ("time, content, from, to, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `from`) as username_from, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `to` ) as username_to, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= `from` ORDER BY main desc LIMIT 1) as image_name_from, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= `to` ORDER BY main desc LIMIT 1) as image_name_to "
        );
        $this->db->from($this->_dbTableMessages);

        if ($where != "")
            $this->db->where($where);

        $this->db->order_by($this->_dbTableMessages . '.id', 'ASC');

        $query = $this->db->get();

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }
           
        return $result;
    }
    
    
    public function getPokes($fromId, $toId)
    {
      
    }
    
    
    public function getUserData($userId){
        $this->db->select("id, username, coins, zip_code, (YEAR(CURRENT_TIMESTAMP ) - YEAR( {$this->_dbTableUsers}.`birthdate` ) ) AS age, description, is_fake ");
        $this->db->from($this->_dbTableUsers);
        $this->db->where('id', $userId);
        $query = $this->db->get();
         if ($query->num_rows() > 0) {
            $result = $query->row();
            $query->free_result();
        } else {
            $result = NULL;
        }
        return $result;
    }
    public function modActivityExists($fromId, $toId) {
        $where = "{$this->_dbTableActivity}.`user_id` = {$fromId} AND {$this->_dbTableActivity}.`fake_user_id` = {$toId}";
        $this->db->select("id");
        $this->db->from($this->_dbTableActivity);
        $this->db->where($where);
        $query = $this->db->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function startModActivity($userId, $fakeId) {
        $id = $this->session->userdata('user_id');
        $data = array(
            'mod_id' => $id,
            'user_id' => $userId,
            'fake_user_id' => $fakeId,
            'active' => 1,
            'taken' => 0
        );
        $this->db->insert($this->_dbTableActivity, $data);
        $msg = "Started";
        return $msg;
    }

    public function takeModActivity($fromId, $toId) {
        $id = $this->session->userdata('user_id');
        $where = "({$this->_dbTableActivity}.`user_id` = {$fromId} AND {$this->_dbTableActivity}.`fake_user_id` = {$toId} AND (`active` = 0 OR `mod_id`=" . $id . ")  AND `taken`=0 )";
        $this->db->select("id, mod_id, active, taken");
        $this->db->from($this->_dbTableActivity);
        $this->db->where($where);
        $query = $this->db->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->row();
            $query->free_result();
        } else {
            $result = NULL;
        }


        if ($result != NULL) {
            if ($result->mod_id == $id) {
                $this->db->update($this->_dbTableActivity, array('active' => 1), array('id' => $result->id));
                $msg = "Resumed";
            } else {

                $this->db->update($this->_dbTableActivity, array('taken' => 1), array('id' => $result->id));
                $data = array(
                    'mod_id' => $id,
                    'user_id' => $fromId,
                    'fake_user_id' => $toId,
                    'active' => 1,
                    'taken' => 0
                );
                $this->db->insert($this->_dbTableActivity, $data);
                $msg = "Taken";
            }
        }else {
                $msg = "Busy";
            }
      return $msg;
    }
   public function send_mod_message($fromId, $toId, $message){
     
       $id = $this->session->userdata('user_id');
       $where="({$this->_dbTableActivity}.mod_id=".$id." AND {$this->_dbTableActivity}.fake_user_id=".$fromId." AND {$this->_dbTableActivity}.user_id=".$toId." AND {$this->_dbTableActivity}.start_time=( SELECT MAX({$this->_dbTableActivity}.start_time) "
           ."FROM {$this->_dbTableActivity} WHERE {$this->_dbTableActivity}.mod_id=".$id." AND {$this->_dbTableActivity}.fake_user_id=".$fromId." AND {$this->_dbTableActivity}.user_id=".$toId."))";
           
       $this->db->select('id');
       $this->db->from($this->_dbTableActivity);
       $this->db->where($where);
       $query = $this->db->get();
       if( $query->num_rows()>0 )
        $activity_id=$query->row()->id;
       else
       {
         $this->startModActivity($toId, $fromId);
         //$activity_id=$this->db->insert_id();
         $this->db->select('id');
          $this->db->from($this->_dbTableActivity);
          $this->db->where($where);
          $query = $this->db->get();
          $activity_id=$query->row()->id;
       }
       $this->db->set('last_activity_time', 'NOW()', FALSE);
       $this->db->where('id',$activity_id);
       $this->db->update($this->_dbTableActivity);
       $query->free_result();
       $this->db->select('mod_id, date, outs');
       $this->db->from($this->_dbTableIns);
       $this->db->where('mod_id', $id);
       $this->db->where("DATE({$this->_dbTableIns}.date) = DATE(NOW())");
       $query = $this->db->get();
       if ($query->num_rows() > 0) {
            $result = $query->row();
            $query->free_result();
            $this->db->update($this->_dbTableIns, array('outs'=>$result->outs+1), array('mod_id'=>$result->mod_id, 'date'=>$result->date));
        } else {
           $this->db->insert($this->_dbTableIns, array('mod_id'=>$id,'outs'=>1));
        }
       $this->insertMessage($fromId, $toId, $message);
   }
   private function insertMessage($fromId, $toId, $message) {

        $data = array(
            'from' => $fromId,
            'to' => $toId,
            'content' => $message,
            'seen' => '0'
        );
        $this->db->insert($this->_dbTableMessages, $data);
    }
    public function getFakeUsers(){
         $team_id = $this->session->userdata('team_id');
         $sql = "
              SELECT DISTINCT id, username FROM {$this->_dbTableUsers} WHERE 
                  {$this->_dbTableUsers}.is_fake=1
                  AND
                      {$this->_dbTableUsers}.team_id= {$team_id}
                  AND {$this->_dbTableUsers}.id NOT IN (SELECT `fake_user_id` FROM {$this->_dbTableActivity})"
             ;
        $query = $this->db->query($sql);
        $result = $query->result();
       if ($query->num_rows() > 0) {
        $result = $query->result();
        $query->free_result();
        } else {
         $result = NULL;
    }

     return $result;
    }
    
    public function getUsers(){
       $sql = "
              SELECT id, username FROM {$this->_dbTableUsers} WHERE 
                 {$this->_dbTableUsers}.user_group=3 AND {$this->_dbTableUsers}.is_fake=0 "
             ;
        $query = $this->db->query($sql);
        $result = $query->result();
       if ($query->num_rows() > 0) {
        $result = $query->result();
        $query->free_result();
        } else {
         $result = NULL;
    }

     return $result;
    }
    
    public function saveDescription($fromId, $description){
       $this->db->update($this->_dbTableUsers, array('description'=>$description), array('id'=>$fromId));
    }
    public function setFakeUserOnline($id){
        
        $time = time();       
        $this->db->select('user_id');
        $this->db->from($this->_dbTableLogOnline);
        $this->db->where('user_id',$id);
        $query=$this->db->get();
        
        if($query->num_rows() == 0){ // Nije online, ubaci u tabelu
            $query->free_result();
            $data['user_id'] = $id;
            $query = $this->db->insert($this->_dbTableLogOnline,$data);
        }
        else // Jeste online update time
        {
          $date_time = date("Y-m-d H:i:s",$time);
          $this->db->where('user_id',$id)
                   ->update($this->_dbTableLogOnline,array('time'=>$date_time ));
        }
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
        } else { }
        
        //$query->free_result();
        
        $refresh_time = $this->config->item('refresh_time');
        $delete_time = date("Y-m-d H:i:s",$time - 2*$refresh_time);
        $this->db->query(" DELETE FROM {$this->_dbTableLogOnline} WHERE '{$delete_time}' > `time`; ");
        
    }
    public function setViewProfile($fromId, $toId){
         $data = array( 
                      'my_id' => $toId, 
                      'user_id' => $fromId
                   );
      $this->db->insert($this->_dbTableProfileViews,$data);
      
      $errors = $this->db->_error_message();
      if (!empty($errors)) {
        throw new Exception("SQL Error: " . $errors);
      } else { }
    }
}